
package vn.aptech.project.model.dao;
import java.sql.SQLException;
import vn.aptech.project.model.dto.*;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

public class  Employee_Detail_DAO extends CommonDAO {
 public  Employee_Detail_DAO() throws ClassNotFoundException, SQLException , IOException{
 }


public Vector selectall() throws SQLException
{
String strSelect = "SELECT * FROM FS_Employee_Detail";
return ExecSelect(strSelect);
}
public Vector totalemp() throws SQLException
{
String strSelect = "select tongnhanvien=count('staffid') from FS_Employee_Detail";
return ExecSelect(strSelect);
}
public Vector SearchEmployeeByName(String staffname) throws SQLException
{
String strSelect = "SELECT * FROM FS_Employee_Detail where staffname like '%"+staffname+"%'";
return ExecSelect(strSelect);
}
public Vector selectEmployeeById(String staffid) throws SQLException
{
String strSelect = "SELECT * FROM FS_Employee_Detail where staffid="+staffid;
return ExecSelect(strSelect);
}
public String getStaffAddByStaffName(String staffname) throws SQLException, FileNotFoundException, IOException
{
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
             } catch (ClassNotFoundException ex)
            {
            Logger.getLogger(tblorder_DAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            String connectionString = "jdbc:sqlserver://localhost:1433;databaseName=FurnitureSystem;user=sa;password=1234567";
            Connection con = DriverManager.getConnection(connectionString);
            Statement statement = con.createStatement();
            String str = "Select addr from FS_Employee_Detail where staffname='"+staffname+"'";
            ResultSet rs = statement.executeQuery(str);
            rs.next();
            Vector vt = new Vector();
            vt.add(rs.getObject("addr"));
            String a =String.valueOf(vt.get(0));
            return a;

}
public int getStaffidByStaffName(String staffname) throws SQLException, FileNotFoundException, IOException
{
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
             } catch (ClassNotFoundException ex)
            {
            Logger.getLogger(tblorder_DAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            String connectionString = "jdbc:sqlserver://localhost:1433;databaseName=FurnitureSystem;user=sa;password=1234567";
            Connection con = DriverManager.getConnection(connectionString);
            Statement statement = con.createStatement();
            String str = "Select staffid from FS_Employee_Detail where staffname='"+staffname+"'";
            ResultSet rs = statement.executeQuery(str);
            rs.next();
            Vector vt = new Vector();
            vt.add(rs.getObject("staffid"));
            int a =Integer.parseInt(String.valueOf(vt.get(0)));
            return a;

}
public int getStaffContactByStaffName(String staffname) throws SQLException, FileNotFoundException, IOException
{
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
             } catch (ClassNotFoundException ex)
            {
            Logger.getLogger(tblorder_DAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            String connectionString = "jdbc:sqlserver://localhost:1433;databaseName=FurnitureSystem;user=sa;password=1234567";
            Connection con = DriverManager.getConnection(connectionString);
            Statement statement = con.createStatement();
            String str = "Select contact_no from FS_Employee_Detail where staffname='"+staffname+"'";
            ResultSet rs = statement.executeQuery(str);
            rs.next();
            Vector vt = new Vector();
            vt.add(rs.getObject("contact_no"));
            int a =Integer.parseInt(String.valueOf(vt.get(0)));
            return a;

}
public String getStaffDepartByStaffName(String staffname) throws SQLException, FileNotFoundException, IOException
{
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
             } catch (ClassNotFoundException ex)
            {
            Logger.getLogger(tblorder_DAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            String connectionString = "jdbc:sqlserver://localhost:1433;databaseName=FurnitureSystem;user=sa;password=1234567";
            Connection con = DriverManager.getConnection(connectionString);
            Statement statement = con.createStatement();
            String str = "Select department from FS_Employee_Detail where staffname='"+staffname+"'";
            ResultSet rs = statement.executeQuery(str);
            rs.next();
            Vector vt = new Vector();
            vt.add(rs.getObject("department"));
            String a =String.valueOf(vt.get(0));
            return a;

}
public Vector SelectPwByName(String UseName) throws SQLException
{
String strSelect = "SELECT passwordemp FROM FS_Employee_Detail where staffname='"+UseName+"'";
return ExecSelect(strSelect);
}
public int insert(Employee_Detail_DTO  obj) throws SQLException
{
String strInsert = "INSERT INTO FS_Employee_Detail VALUES (N'"+  obj.getstaffname() +"'  ,N'"+ obj.getaddr() +"'  ,"+ obj.getcontact_no() +" ,N'"+ obj.getpasswordemp() +"'  ,N'"+ obj.getdepartment() +"' )";
return  ExecNonQuery(strInsert);
}


public int update(Employee_Detail_DTO  obj) throws SQLException
{
String strUpdate = "UPDATE FS_Employee_Detail SET staffname= N'"+ obj.getstaffname()+"' ,"+"addr= N'"+obj.getaddr() +"' ,"+"contact_no= "+obj.getcontact_no() +","+"passwordemp= N'"+obj.getpasswordemp() +"' ,"+"department= N'"+obj.getdepartment() +"' WHERE staffid="+ obj.getstaffid() ;
return  ExecNonQuery(strUpdate);
}


public int delete(int staffid) throws SQLException
{
String strDelete = "DELETE FROM FS_Employee_Detail WHERE staffid="+staffid;
return  ExecNonQuery(strDelete);
}
}

